package top.lunarye.modules.award.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import top.lunarye.modules.award.entity.Award;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import top.lunarye.modules.award.entity.dto.AwardAnalysisNameDTO;
import top.lunarye.modules.award.entity.dto.AwardAnalysisTypeDTO;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 成果表  Mapper 接口
 * </p>
 *
 * @author lunar
 * @since 2021-07-23
 */
@Mapper
public interface AwardMapper extends BaseMapper<Award> {

    @Select("select type, count(id) num from t_award where is_delete = 0 group by type")
    List<AwardAnalysisTypeDTO> analysisGroupByType();

    @Select("select count(aw.id) as num, st.id as staffId, st.name as name, st.staff_number as staffNumber\n" +
            "from t_award aw left join t_staff st on aw.staff_id = st.id\n" +
            "where aw.is_delete = 0\n" +
            "group by st.id")
    List<AwardAnalysisNameDTO> analysisGroupByStaffName();

    @Select("select count(aw.id) as num, st.id as staffId, st.name as name, st.staff_number as staffNumber\n" +
            "from t_award aw left join t_staff st on aw.staff_id = st.id\n" +
            "where aw.is_delete = 0\n" +
            "and st.name like CONCAT('%',#{name},'%')\n" +
            "group by st.id\n" +
            "limit #{current},#{size}")
    List<AwardAnalysisNameDTO> analysisByName(String name, Integer current, Integer size);

    @Select("select count(aw.id) as num, st.id as staffId, st.name as name, st.staff_number as staffNumber\n" +
            "from t_award aw left join t_staff st on aw.staff_id = st.id\n" +
            "where aw.is_delete = 0\n" +
            "and st.name like CONCAT('%',#{name},'%')\n" +
            "and get_time between #{startTime} and #{endTime}\n" +
            "group by st.id\n" +
            "limit #{current},#{size}")
    List<AwardAnalysisNameDTO> analysisByNameLimitByDate(String name, Integer current,
                                                         Integer size, Date startTime, Date endTime);

    @Select("select count(*) from " +
            "(select count(*) from t_award aw left join t_staff st on aw.staff_id = st.id " +
            "where aw.is_delete = 0 and st.name like CONCAT('%',#{name},'%') " +
            "group by st.id) newtable")
    Integer getAnalysisByNameSize(String name);

    @Select("select count(*) from " +
            "(select count(*) from t_award aw left join t_staff st on aw.staff_id = st.id " +
            "where aw.is_delete = 0 and st.name like CONCAT('%',#{name},'%') " +
            "and get_time between #{startTime} and #{endTime}\n" +
            "group by st.id) newtable")
    Integer getAnalysisByNameSizeLimitByDate(String name, Date startTime, Date endTime);

    @Select("select count(aw.id) as num, st.id as staffId, st.name as name, st.staff_number as staffNumber\n" +
            "from t_award aw left join t_staff st on aw.staff_id = st.id\n" +
            "where aw.is_delete = 0\n" +
            "and get_time between #{startTime} and #{endTime}\n" +
            "group by st.id")
    List<AwardAnalysisNameDTO> analysisGroupByStaffNameLimitByDate(Date startTime, Date endTime);

    @Select("select type, count(id) num from t_award where is_delete = 0 \n" +
            "and get_time between #{startTime} and #{endTime}\n" +
            "group by type")
    List<AwardAnalysisTypeDTO> analysisGroupByTypeLimitByDate(Date startTime, Date endTime);

    @Select("SELECT type, count(id) num FROM t_award WHERE is_delete=0 AND " +
            "(get_time BETWEEN #{startTime} AND #{endTime} AND staff_id = #{staffId}) " +
            "GROUP BY type")
    List<AwardAnalysisTypeDTO> getStaffAwardAnalysisLimitByDate(Integer staffId, Date startTime, Date endTime);

    @Select("SELECT type, count(id) num FROM t_award WHERE is_delete=0 AND " +
            "(staff_id = #{staffId}) " +
            "GROUP BY type")
    List<AwardAnalysisTypeDTO> getStaffAwardAnalysis(Integer staffId);
}
